package com.etc.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ResourceBundle;
import javax.sql.rowset.CachedRowSet;
import com.sun.rowset.CachedRowSetImpl;
/**
 * MySQL数据库工具类
 * @author Administrator
 *
 */
public class DBUtil {

	private static  String driverClassName;
	private static  String url;
	private static  String username;
	private static  String password;
	
	/**
	 * 配置数据库连接信息
	 */
	private static void loadDBProperties() {
		ResourceBundle resourceBundle = ResourceBundle.getBundle("db");// 资源包的基本名称，一个完全限定的类名
		driverClassName = resourceBundle.getString("jdbc.driverClassName");
		url = resourceBundle.getString("jdbc.url");
		username = resourceBundle.getString("jdbc.username");
		password = resourceBundle.getString("jdbc.password");
		
	}
	
	// 读取数据库连接信息
	static {
	
		loadDBProperties();
	}
	
	/**
	 * 返回一个数据库连接
	 * @return
	 */
	public static Connection getConnection() {
		Connection connection = null;
		try {
			Class.forName(driverClassName);
			connection = DriverManager.getConnection(url, username, password);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return connection;
	}
	
	/**
	 * 专门用于SQL查询操作
	 * @param sql    执行的SQL查询语句
	 * @param params SQL查询语句所需的参数
	 * @return
	 */
	public static ResultSet query(String sql, Object... params) {
		
		Connection connection = null;	
		PreparedStatement preparedStatement = null;	
		ResultSet resultSet = null;		
		CachedRowSet cacheRowSet = null;
		try {
			// 连接数据库
			connection = getConnection();
			// 创建预编译语句对象
			preparedStatement =connection.prepareStatement(sql);
			
			// 循环可变参数数组,对SQL的占位符?赋值
			for (int i = 0; i < params.length; i++) {
				preparedStatement.setObject(i+1,params[i]);
			}
			
			// 执行SQL语句得到ResultSet结果集
			resultSet = preparedStatement.executeQuery();
			
			// 将结果集ResultSet中的数据放到缓存结果集CachedRowSet中
			// 创建一个CachedRowSet对象 
			cacheRowSet = new CachedRowSetImpl(); 
			// void populate(ResultSet data)throws SQLException填充此CachedRowSet从给定数据对象ResultSet对象。
			cacheRowSet.populate(resultSet);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 释放资源
			close(resultSet, preparedStatement, connection);
		}
		
		return cacheRowSet; // 返回缓存结果集
	}
	/**
	 * 专门用于SQL更新insert/update/delete操作
	 * @param sql    执行的SQL insert/update/delete语句
	 * @param params SQL语句所需的参数
	 * @return
	 */
	public static int update(String sql, Object... params) {
		int result = 0;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
	    
		try {
			// 1.连接数据库
			connection = getConnection();
			// 得到预编译语句对象,执行SQL语句
			preparedStatement = connection.prepareStatement(sql);
			// 对?赋值
			for (int i = 0; i < params.length; i++) {
				preparedStatement.setObject(i + 1, params[i]);
			}
			// 执行
			result = preparedStatement.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 释放资源
			close(null, preparedStatement, connection);
		}
		return result;
	}
	/**
	 * 释放资源
	 * @param resultSet
	 * @param preparedStatement
	 * @param connection
	 */
	public static void close(ResultSet resultSet, PreparedStatement preparedStatement, Connection connection) {
		try {
			if (resultSet != null) {
				resultSet.close();
			}
			if (preparedStatement != null) {
				preparedStatement.close();
			}
			if (connection != null) {
				connection.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
